﻿using CallCenter.Data.Base;
using CallCenter.Data.BizUtilities.Base;
using CallCenter.Data.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CallCenter.Data.BizUtilities
{
    public class ContactDetailUtility : BaseUtility
    {
        public ContactDetailUtility(DataManager manager)
            : base(manager)
        { }

        public ReturnMessage<ContactDetailsVM> InsertToContactDetails(ContactDetailsVM model)
        {
            var returnMessage = new ReturnMessage<ContactDetailsVM>();
            int res = 0;
            int companyId = 33;
            if (model.companyId.Equals(""))
                companyId = model.companyId;
            int officeId = Convert.ToInt32(model.officeId);
            if (model.email.Equals(""))
                res++;

           

            if (String.IsNullOrEmpty(model.homePhone))
                model.homePhone = "";
            if (String.IsNullOrEmpty(model.faxNumber))
                model.faxNumber = "";

            if (String.IsNullOrEmpty(model.workPhone))
                model.workPhone = "";
            if (String.IsNullOrEmpty(model.timeZone))
                model.timeZone = "";

            if (String.IsNullOrEmpty(model.notes))
                model.notes = "";

            if (res != 0)
            {
                returnMessage.Message = "Email cannot be empty";

            }
            var id = this.Insert(model.contactId, model.companyId, model.timeZone, officeId, model.contactTypeId,
                   model.homePhone, model.workPhone, model.faxNumber, model.email, model.title, model.startDate, model.endDate, model.notes);
            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;
                DateTime EndDate = model.startDate.Value.AddDays(-1);
                this.Bridge.CompanyContactsUtility.UpdateIsInActive(model.companyId, model.contactId);
                string JobTitle = this.Bridge.JobTitleUtility.GetJobtitleByJobId(model.title);
                this.Bridge.ccContactUtility.UpdateccContactsExceptName(model.email, model.companyId, model.contactTypeId,
                     model.startDate, model.workPhone, model.homePhone, model.office, model.notes, model.contactId, JobTitle);
                this.updateAllEndDateExceptContactID(id, model.contactId, EndDate);
                this.Bridge.CompanyContactsUtility.Insert(model.companyId, model.contactId);
                returnMessage.Message = "Recordhas been  inserted to database";
                returnMessage.Status = true;
            }
            return returnMessage;

        }

        private void updateAllEndDateExceptContactID(int id, int Contactid, DateTime EndDate)
        {
            int nRtn = -1;

            string sql = "UPDATE contactDetail SET " +
                 " endDate= @endDate" +
                 " WHERE id <> @id and contactid = @Contactid and endDate is null";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@endDate", SqlDbType.SmallDateTime, 10, EndDate);
                this.Factory.AddParameter(cmd, "@Contactid", SqlDbType.Int, 10, Contactid);

                this.Factory.AddParameter(cmd, "@id", SqlDbType.Int, 10, id);
                this.Factory.ExecuteNonQuery(cmd);

            }
            nRtn = id;
        }


        public ReturnMessage<ContactDetailsVM> UpdateToContactDetails(ContactDetailsVM model)
        {
            var returnMessage = new ReturnMessage<ContactDetailsVM>();
            int res = 0;
            int officeId = Convert.ToInt32(model.officeId);
            if (model.email.Equals(""))
                res++;
            

            if (String.IsNullOrEmpty(model.homePhone))
                model.homePhone = "";

            if (String.IsNullOrEmpty(model.workPhone))
                model.workPhone = "";

            if (String.IsNullOrEmpty(model.notes))
                model.notes = "";

            if (String.IsNullOrEmpty(model.faxNumber))
                model.faxNumber = "";
            if (res != 0)
                returnMessage.Message = "Email cannot be empty";

            var id = this.update(model.id, model.contactId, model.companyId, model.timeZone, officeId, model.contactTypeId,
             model.homePhone, model.workPhone, model.faxNumber, model.email, model.title, model.startDate, model.endDate, model.notes);

            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;
                returnMessage.Message = "Record has been insert data to database";
                returnMessage.Status = true;
            }
            return returnMessage;
        }


        public ReturnMessage<ContactCompanyMessageVM> InsertToContactMessages(ContactCompanyMessageVM model)
        {
            var returnMessage = new ReturnMessage<ContactCompanyMessageVM>();
          
            if (model.emailTo == "")
                model.emailTo = "resnav@resnav.com";

            if (String.IsNullOrEmpty(model.phone))
                model.phone = "";
            if (String.IsNullOrEmpty(model.notes))
                model.notes = "";
            if (String.IsNullOrEmpty(model.subject))
                model.subject = "";
            if (String.IsNullOrEmpty(model.logInOwner))
                model.logInOwner = "";

            if (String.IsNullOrEmpty(model.logInOwner))
                model.logInOwner = "";
            if (String.IsNullOrEmpty(model.note))
                model.note = "";





            //if (model.followUpdate.CompareTo(DateTime.MinValue) == 0)
            //    model.followUpdate = DateTime.Now;


            //if (model.communicationDate.CompareTo(DateTime.MinValue) == 0)
            //    model.communicationDate = DateTime.Now;
            var id = this.Bridge.CommunicationUtility.Insert(model.userID,model.logInOwner, model.createdDate,"",model.notes, model.clientID,
                           model.contactID, model.commId, model.communicationTypeID, model.subject, DateTime.Now, model.assignedEmployeeID, model.productID, model.officeId, model.notes, model.followUpdate, model.statusID, model.phone, model.emailTo);
            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;
                returnMessage.Status = true;

                //Comm Employee Contacts iteration
                if (!string.IsNullOrEmpty(model.CommEmployeeIds))
                {
                    foreach (var item in model.CommEmployeeIds.Split(','))
                    {
                        int commId = id;
                        int comEmpId = Convert.ToInt32(item);
                        this.Bridge.CommunicationContacts.Insert(commId, comEmpId, DateTime.Now);

                    }
                }
                returnMessage.Message = "Record has been inserted";
            }
            return returnMessage;
        }

        public ReturnMessage<ContactCompanyMessageVM> UpdateToContactMessages(ContactCompanyMessageVM model)
        {
            var returnMessage = new ReturnMessage<ContactCompanyMessageVM>();

            if (String.IsNullOrEmpty(model.phone))
                model.phone = "";
            if (String.IsNullOrEmpty(model.notes))
                model.notes = "";
            if (String.IsNullOrEmpty(model.subject))
                model.subject = "";

           


            if (String.IsNullOrEmpty(model.logInOwner))
                model.logInOwner = "";


            //if (model.followUpdate.CompareTo(DateTime.MinValue) == 0)
            //    model.followUpdate = DateTime.Now;


            //if (model.communicationDate.CompareTo(DateTime.MinValue) == 0)
            //    model.communicationDate = DateTime.Now;

            if (model.emailTo == "")
                model.emailTo = "resnav@resnav.com";

            var id = this.Bridge.CommunicationUtility.UpdateById(model.id, model.userID,model.logInOwner, model.clientID, model.contactID, model.communicationTypeID, model.subject, model.assignedEmployeeID, model.productID, model.officeId, model.notes, model.followUpdate,model.communicationDate, model.statusID, model.phone, model.emailTo);
            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;
                returnMessage.Status = true;
                this.DeleteCommContact(model.id);


                //Comm Employee Contacts iteration
                if (!string.IsNullOrEmpty(model.CommEmployeeIds))
                {
                    foreach (var item in model.CommEmployeeIds.Split(','))
                    {
                        int commId = id;
                        int comEmpId = Convert.ToInt32(item);
                        this.Bridge.CommunicationContacts.Insert(commId, comEmpId, DateTime.Now);

                    }
                }
                returnMessage.Message = "Record has been inserted";

            }
            return returnMessage;
        }


        public List<ContactDetailsVM> getAll(int contactId)
        {
            string sql = "SELECT cd.*, c.firstName+' '+c.lastName as name, jt.code as code, cp.name as company, ct.description as companyType,co.name as companyoffice " +
                " FROM contactDetail cd " +
                " INNER JOIN ddlCompany cp on cp.id = cd.companyId " +
                " INNER JOIN ddlContactTypes ct on ct.id= cd.contactTypeId " +
                " INNER JOIN ccContacts c ON c.id = cd.contactid " +
                " INNER JOIN ddlJobTitle jt on jt.id = cd.title " +
                " LEFT OUTER JOIN ccCompanyOffice co ON co.id = cd.officeId" +
                " WHERE cd.contactId = " + contactId
                + " order by cd.endDate";

            return this.Factory.ExecuteObjects<ContactDetailsVM>(sql);
        }


        public void DeleteCommContact(int id) //delete the row of data using the id passed to the function
        {

            string sql = "Delete from ccCommcontact where commId = @ID";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, id);

                this.Factory.ExecuteNonQuery(cmd);
            }
        }


        public List<JobTitleViewModel> GetAllJobTitlesByID(int contactId)
        {
            string sql = "SELECT id, code FROM ddlJobTitle";

            return this.Factory.ExecuteObjects<JobTitleViewModel>(sql);
        }

        public List<JobTitleViewModel> GetAllJobTitles()
        {
            string sql = "SELECT id, code FROM ddlJobTitle";

            return this.Factory.ExecuteObjects<JobTitleViewModel>(sql);
        }


        public List<CompanyOfficeViewModel> GetBranchOffices()
        {
            string sql = "SELECT 0 as id, 'None' as companyoffice UNION ALL SELECT id, cf.name as companyoffice FROM ccCompanyOffice cf ORDER BY companyoffice DESC";

            return this.Factory.ExecuteObjects<CompanyOfficeViewModel>(sql);
        }

        public List<CompanyGridViewModel> GetAllCompanies()
        {
            string sql = "SELECT id, c.name company FROM ddlCompany c WHERE name like 'None' UNION ALL SELECT id, name as company FROM ddlCompany WHERE name <> 'None' Order By company ";
            return this.Factory.ExecuteObjects<CompanyGridViewModel>(sql);
        }


        public int Insert(int contactId, int companyId, string timeZone, int officeId, int contactTypeId, string homePhone,
       string workPhone, string fax, string email, int title, Nullable<DateTime> startDate, Nullable<DateTime> endDate, string notes)
        {
            int nRtn = -1;

            string sql = "INSERT INTO contactDetail " +
                     "(contactId, companyId, officeId, contactTypeId, timeZone, Email, homePhone, workPhone," +
                     " faxNumber, title, startDate, endDate, notes) " +
                     "VALUES(@contactId, @companyId, @officeId, @contactTypeId, @timeZone, @Email, @homePhone, @workPhone," +
                     " @faxNumber, @title, @startDate, @endDate, @notes) " +
                     "SELECT @pResult =@@IDENTITY";
            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@contactId", SqlDbType.Int, 10, contactId);
                this.Factory.AddParameter(cmd, "@companyId", SqlDbType.Int, 100, companyId);
                this.Factory.AddParameter(cmd, "@officeId", SqlDbType.Int, 10, officeId);
                this.Factory.AddParameter(cmd, "@contactTypeId", SqlDbType.Int, 100, contactTypeId);
                this.Factory.AddParameter(cmd, "@timeZone", SqlDbType.NVarChar, 10, timeZone);
                this.Factory.AddParameter(cmd, "@Email", SqlDbType.NVarChar, 100, email);
                this.Factory.AddParameter(cmd, "@homePhone", SqlDbType.NVarChar, 10, homePhone);
                this.Factory.AddParameter(cmd, "@workPhone", SqlDbType.NVarChar, 10, workPhone);
                this.Factory.AddParameter(cmd, "@faxNumber", SqlDbType.NVarChar, 10, fax);
                this.Factory.AddParameter(cmd, "@title", SqlDbType.Int, 10, title);

                this.Factory.AddParameter(cmd, "@notes", SqlDbType.NVarChar, 1000, notes);

                if (startDate.Value.CompareTo(DateTime.MinValue) == 0)
                    startDate = DateTime.Now;
                this.Factory.AddParameter(cmd, "@startDate", SqlDbType.SmallDateTime, 10, startDate);

                //if (endDate.Equals(DateTime.MinValue))
                //    this.Factory.AddParameter(cmd, "@endDate", SqlDbType.SmallDateTime, 10, DBNull.Value);
                //else
                //    this.Factory.AddParameter(cmd, "@endDate", SqlDbType.SmallDateTime, 10, endDate);

                this.Factory.AddParameter(cmd, "@endDate", SqlDbType.SmallDateTime, 10, DBNull.Value);


                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }
            return nRtn;

        }

        public int update(int id, int contactId, int companyId, string timeZone, int officeId, int contactTypeId, string homePhone, string workPhone,
                    string fax, string email, int title, Nullable<DateTime> startDate, Nullable<DateTime> endDate, string notes)
        {
            int nRtn = -1;

            string sql = "UPDATE contactDetail SET " +
                 "contactId = @contactId, companyId = @companyId, timeZone = @timeZone, officeId=@officeId, contactTypeId=@contactTypeId," +
                 " homePhone=@homePhone, workPhone=@workPhone, faxNumber=@faxNumber,  Email=@Email, title=@title, startDate=@startDate, endDate=@endDate, notes=@notes " +
                 " WHERE id = @id";


            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@contactId", SqlDbType.Int, 10, contactId);
                this.Factory.AddParameter(cmd, "@companyId", SqlDbType.Int, 100, companyId);
                this.Factory.AddParameter(cmd, "@officeId", SqlDbType.Int, 10, officeId);
                this.Factory.AddParameter(cmd, "@contactTypeId", SqlDbType.Int, 100, contactTypeId);
                this.Factory.AddParameter(cmd, "@timeZone", SqlDbType.NVarChar, 10, timeZone);
                this.Factory.AddParameter(cmd, "@Email", SqlDbType.NVarChar, 100, email);
                this.Factory.AddParameter(cmd, "@homePhone", SqlDbType.NVarChar, 10, homePhone);
                this.Factory.AddParameter(cmd, "@workPhone", SqlDbType.NVarChar, 10, workPhone);
                this.Factory.AddParameter(cmd, "@faxNumber", SqlDbType.NVarChar, 10, fax);
                this.Factory.AddParameter(cmd, "@title", SqlDbType.Int, 10, title);

                this.Factory.AddParameter(cmd, "@notes", SqlDbType.NVarChar, 1000, notes);


             

                if ((startDate == null) || (startDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@startDate", SqlDbType.SmallDateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@startDate", SqlDbType.SmallDateTime, 10, startDate);


                if ((endDate == null) || (endDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@endDate", SqlDbType.SmallDateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@endDate", SqlDbType.SmallDateTime, 10, endDate);

                this.Factory.AddParameter(cmd, "@id", SqlDbType.Int, 10, id);
                this.Factory.ExecuteNonQuery(cmd);

            }
            nRtn = id;
            return nRtn; //retruning the data stored in the table
        }



        public void delete(int id)
        {
            string sql = "DELETE FROM contactDetail " +
                   "WHERE ID = @ID ";
            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, id);
                this.Factory.ExecuteNonQuery(cmd);
            }

        }

        public void DeleteAll() //delete all the rows in the data.
        {
            string sql = "DELETE FROM contactDetail ";

            this.Factory.ExecuteNonQuery(sql);

        }


    }
}
